package com.group.board;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class BoardMgr {//db와 연결해서 처리 할 부분
	
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	private DataSource ds;
	
	int tot =0;
	int plist = 5; //페이지 당 출력 레코드
	int pageSu = 0;
	
	public BoardMgr(){
		try{
			//pooling 처리:DBCP(DataBase Connect Pooling)
				Context context= new InitialContext();
				ds =(DataSource)context.lookup("java:comp/env/jdbc/Oracle10g");
				
			}catch(Exception e){
				System.out.println("연결 오류:" + e.getMessage());
				return;
			}
	}
	//현재 레코드의 최대 번호 구하기 : 추가 작업시 필요
	public int currentGetMaxNum(){
		int cnt = 0;
		String sql = "select max(num) from boarddata";
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()) cnt = rs.getInt(1); //자료가 있으면 가장 큰 번호를 가져간다. 저 1은 max!!
			
		} catch (Exception e) {
			System.out.println("currentGetMaxNum 오류 : " +e.getMessage());
		}finally{
			try{
				
			if(rs != null)rs.close();
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		
		}
	}	
	return cnt;	
	}
	
	
	public void saveData(BoardData data){ //db에 저장
		String sql = "insert into boarddata values(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, data.getNum());
			pstmt.setString(2, data.getName());
			pstmt.setString(3, data.getPass());
			pstmt.setString(4, data.getMail());
			pstmt.setString(5, data.getTitle());
			pstmt.setString(6, data.getCont());
			pstmt.setString(7, data.getBip());
			pstmt.setString(8, data.getBdate());
			pstmt.setInt(9, 0); //readcnt
			pstmt.setInt(10, data.getGnum());
			pstmt.setInt(11, 0); //onum
			pstmt.setInt(12, 0); //nested
			
			pstmt.executeUpdate();//추가완료
			
		} catch (Exception e) {
			System.out.println("saveData 오류:" + e.getMessage());
		}finally{
			try{
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		
		}
		}
	}
	//레코드 전체 개수
	public void totalList(String word, String type){
		String sql = "select count(*) from boarddata";
		try {
			conn = ds.getConnection();
			
			if(word == null){
				pstmt = conn.prepareStatement(sql);
			}else{//검색인 경우. 하단에 where 와 like 앞에 한칸 띄움
				sql += " where " + type + " like ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+word+"%");
			}
			rs = pstmt.executeQuery();
			rs.next();
			tot = rs.getInt(1);
			//System.out.println("전체 레코드 수:" + tot);
		} catch (Exception e) {
			System.out.println("totalList 오류:" + e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		
		}
		}
	}
	
	
	public void setPlist(int plist) {
		this.plist = plist;
	}
	
	public int getPagesu(){  //페이지 수 구하기
		pageSu = tot/plist;
		if(tot%plist>0) ++pageSu;
		return pageSu;
	}
	public ArrayList<BoardData> getList(String word,String type, int page){
		//int page는 되돌아 갔을때도 그 페이지로 돌아 갈 수 있게 하기 위함
		String sql = "select * from boarddata";
		ArrayList<BoardData> list = new ArrayList<BoardData>();
		try {
			conn = ds.getConnection();
			
			if(word == null){
				sql += " order by gnum desc,onum asc";
				pstmt = conn.prepareStatement(sql);
			}else{ //검색인 경우
				sql += " where " + type + " like ?";
				sql += " order by gnum desc,onum asc";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+word+"%");
			}
			rs = pstmt.executeQuery();
			for(int i=0; i<(page - 1)*plist;i++){
				rs.next(); // plist에 따른 포인터 위치 0부터 시작 plist가 1: 이동없음, 2:4
			}
			int k =0;
			while(rs.next() && k< plist){
				BoardData data = new BoardData();
				data.setNum(rs.getInt("num"));
				data.setName(rs.getString("name"));
				data.setTitle(rs.getString("title"));
				data.setBdate(rs.getString("bdate"));
				data.setReadcnt(rs.getInt("readcnt"));
				data.setNested(rs.getInt("nested")); //들여쓰기용
				list.add(data);
				k++;
			}
		} catch (Exception e) {
			System.out.println("getList 오류:" + e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}
		return list;
	}
	
	//해당 글 자세히 보기
	public void updateReadCnt(String num){ //조회수 증가
		String sql = "update boarddata set readcnt=readcnt+1 where num="+num;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate(); // 수정 완료
		} catch (Exception e) {
			System.out.println("updateReadCnt 에러 : " + e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}
	}
	public BoardData getData(String num){
		BoardData data = null;
		String sql = "select * from boarddata where num=" + num;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				data = new BoardData();
				data.setName(rs.getString("name"));
				data.setPass(rs.getString("pass"));
				data.setMail(rs.getString("mail"));
				data.setTitle(rs.getString("title"));
				data.setCont(rs.getString("cont"));
				data.setBip(rs.getString("bip"));
				data.setBdate(rs.getString("bdate"));
				data.setReadcnt(rs.getInt("readcnt"));				
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}
		return data;
	}
	//댓글용
	public BoardData getReplyData(String num){
		String sql = "select * from boarddata where num="+num;
		BoardData data = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				data = new BoardData();
				data.setTitle(rs.getString("title"));
				data.setGnum(rs.getInt("gnum"));
				data.setOnum(rs.getInt("onum"));
				data.setNested(rs.getInt("nested"));
			}
		} catch (Exception e) {
			System.out.println("getReplyData 오류 : "+e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}		
		return data;
	}
	public void updateOnum(int gnum, int onum){//onum갱신
		//같은 그룹의 레코드는 모두 작업에 참여
		//답변글의 onum은 이미 db에 있는 onum보다 크거나 같은 값을 변경
		String sql = "update boarddata set onum = onum + 1 where onum >= ? and gnum=?";
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, onum);
			pstmt.setInt(2, gnum);
			pstmt.executeUpdate();
		} catch (Exception e) {
			System.out.println("updateOnum 오류 : "+e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}		
	}
	public void saveReplyData(BoardData data){
		String sql = "insert into boarddata values(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, data.getNum());
			pstmt.setString(2, data.getName());
			pstmt.setString(3, data.getPass());
			pstmt.setString(4, data.getMail());
			pstmt.setString(5, data.getTitle());
			pstmt.setString(6, data.getCont());
			pstmt.setString(7, data.getBip());
			pstmt.setString(8, data.getBdate());
			pstmt.setInt(9, 0); //readcnt(조회수)			
			pstmt.setInt(10, data.getGnum());
			pstmt.setInt(11, data.getOnum());
			pstmt.setInt(12, data.getNested());
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("saveReplyData 오류 : "+e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}		
	}
	//수정작업용
	public BoardData getEditData(String num){
		String sql = " select name,pass,mail,title,cont from boarddata where num="+num;
		BoardData data = null;
		try {
			conn = ds.getConnection(); 
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				data = new BoardData();
				data.setName(rs.getString("name"));
				data.setPass(rs.getString("pass"));
				data.setMail(rs.getString("mail"));
				data.setTitle(rs.getString("title"));
				data.setCont(rs.getString("cont"));
			}
		} catch (Exception e) {
			System.out.println("getEditData 오류 : "+e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}return data;
		}

	public boolean checkPass(int num, String new_pass){	//비밀번호 비교
		boolean b = false;
		String sql = "select pass from boarddata where num="+num;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				if(new_pass.equals(rs.getString("pass"))) b = true;
			}			
		} catch (Exception e) {
			System.out.println("checkPass 오류 : "+e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}return b;
		}
	
	public void editData(BoardData data){
		String sql = "update boarddata set name=?,mail=?,title=?,cont=? where num=?";
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, data.getName());
			pstmt.setString(2, data.getMail());
			pstmt.setString(3, data.getTitle());
			pstmt.setString(4, data.getCont());
			pstmt.setInt(5, data.getNum());
			pstmt.executeUpdate();			
		} catch (Exception e) {
			System.out.println("editData 오류 : "+e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}
		}
	public boolean delData(String num, String pass ){
		boolean del = false;
		String sql = "delete from boarddata where num= '"+num+"' and pass= '"+pass+"'";
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				del = true;
			}else {
				del = false;
			}
		} catch (Exception e) {
			System.out.println("delData 오류 : "+e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		}
		}
		return del;
	}
}
